﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EntityFramework.Extensions;
using System.Threading.Tasks;
using System.Threading;
using System.Data.SqlClient;
using System.Data;
using XLugia.Lib.XDataBase.Base.Lib;
using XLugia.Lib.XDataBase.Base.Model;

namespace XLugia.Lib.XDataBase.Base.Lib
{
    /// <summary>
    /// 数据库脚本执行控制器
    /// </summary>
    public class DatabaseExecutor : IDisposable
    {
        /// <summary>
        /// 数据库连接器
        /// </summary>
        public DatabaseConnector databaseConnector;

        public DatabaseExecutor(DatabaseConnector databaseConnector)
        {
            this.databaseConnector = databaseConnector;
        }

        /// <summary>
        /// 创建一个原始 SQL 查询，该查询将返回给定泛型类型的元素。类型可以是包含与从查询返回的列名匹配的属性的任何类型，也可以是简单的基元类型。
        /// </summary>
        /// <typeparam name="T">指定查询所返回对象的类型。</typeparam>
        /// <param name="scriptModel">一个 DatabaseScriptModel 对象。</param>
        /// <returns>返回查询结果。</returns>
        public T sqlQuery<T>(DatabaseScriptModel scriptModel)
        {
            try
            {
                using (var dbo = new DatabaseEntities(databaseConnector))
                using (var transaction = dbo.BeginTransaction(databaseConnector.isolationLevel))
                {
                    if (scriptModel.parameters == null)
                    {
                        return dbo.Database.SqlQuery<T>(scriptModel.command).FirstOrDefault<T>();
                    }

                    return dbo.Database.SqlQuery<T>(scriptModel.command, scriptModel.parameters).FirstOrDefault<T>();
                }
            }
            catch (Exception ex)
            {
                throw DatabaseException.getSqlException("sqlQuery", scriptModel, ex);
            }
        }

        /// <summary>
        /// 创建一个原始 SQL 查询，该查询将返回给定泛型类型的元素集合。类型可以是包含与从查询返回的列名匹配的属性的任何类型，也可以是简单的基元类型。
        /// </summary>
        /// <typeparam name="T">指定查询所返回对象的类型。</typeparam>
        /// <param name="scriptModel">一个 DatabaseScriptModel 对象。</param>
        /// <returns>返回查询结果集合。</returns>
        public List<T> sqlQueryToList<T>(DatabaseScriptModel scriptModel)
        {
            try
            {
                using (var dbo = new DatabaseEntities(databaseConnector))
                using (var transaction = dbo.BeginTransaction(databaseConnector.isolationLevel))
                {
                    if (scriptModel.parameters == null)
                    {
                        return dbo.Database.SqlQuery<T>(scriptModel.command).ToList<T>();
                    }
                    return dbo.Database.SqlQuery<T>(scriptModel.command, scriptModel.parameters).ToList<T>();
                }
            }
            catch (Exception ex)
            {
                throw DatabaseException.getSqlException("sqlQueryToList", scriptModel, ex);
            }
        }

        /// <summary>
        /// 对数据库执行给定的 DDL/DML 命令，多个DatabaseScriptModel在一个事务里执行。
        /// </summary>
        /// <param name="scriptModel">一个 List<DatabaseScriptModel> 对象。</param>
        public void executeSqlCommand(List<DatabaseScriptModel> scriptModels)
        {
            try
            {
                using (var dbo = new DatabaseEntities(databaseConnector))
                using (var transaction = dbo.BeginTransaction())
                {
                    string command = "";
                    try
                    {
                        foreach (var scriptModel in scriptModels)
                        {
                            DateTime sqlScriptStartTime = DateTime.Now;
                            command = scriptModel.command;
                            if (scriptModel.parameters == null)
                            {
                                dbo.Database.ExecuteSqlCommand(scriptModel.command);
                            }
                            else
                            {
                                dbo.Database.ExecuteSqlCommand(scriptModel.command, scriptModel.parameters);
                            }
                        }
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw DatabaseException.getSqlException("executeSqlCommand Rollback", command, ex);
                    }
                }
            }
            catch (Exception ex)
            {
                throw DatabaseException.getSqlException("executeSqlCommand", scriptModels, ex);
            }
        }

        /// <summary>
        /// 不使用事务对数据库执行给定的 DDL/DML 命令集合。
        /// </summary>
        /// <param name="scriptModel">一个 List<DatabaseScriptModel> 对象。</param>
        public void executeSqlCommandWithOutTransaction(List<DatabaseScriptModel> scriptModels)
        {
            try
            {
                using (var dbo = new DatabaseEntities(databaseConnector))
                {
                    string command = "";
                    foreach (var scriptModel in scriptModels)
                    {
                        DateTime sqlScriptStartTime = DateTime.Now;
                        command = scriptModel.command;
                        if (scriptModel.parameters == null)
                        {
                            dbo.Database.ExecuteSqlCommand(scriptModel.command);
                        }
                        else
                        {
                            dbo.Database.ExecuteSqlCommand(scriptModel.command, scriptModel.parameters);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw DatabaseException.getSqlException("executeSqlCommand", scriptModels, ex);
            }
        }

        /// <summary>
        /// 资源释放
        /// </summary>
        public void Dispose()
        {
            this.databaseConnector.Dispose();
            this.databaseConnector = null;
        }
    }
}
